package com.zr.daoimp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.dao.ClosedDaoYR;
import com.zr.model.ClosedYR;
import com.zr.model.User;
import com.zr.util.JDBCUtil;

/**
 * closed表的Dao层实现
 * @author YR
 *
 */
public class ClosedDaoImplYR implements ClosedDaoYR{

	@Override
	public void recordclo(int mid, int uid, int urole, String cdate, String ccontent) {
		// sql语句,在colsed表中添加一条封号记录
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT into dcxt_closed");
		sql.append(" (mid,uid,urole,cdate,ccontent) values(?,?,?,?,?)");
		// 通过工具包建立connection
		Connection con = JDBCUtil.getConnection();
		// 声明预编译器
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,mid);
			pst.setInt(2,uid);
			pst.setInt(3,urole);
			pst.setString(4,cdate);
			pst.setString(5,ccontent);
		    pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}

	@Override
	public List<ClosedYR> selclouserByPage(int page, int rows) {
		//新建一个ClosedYR集合
		List<ClosedYR> lc = new ArrayList<ClosedYR>();
		// sql语句,查询出某页的已封记录Closed
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM dcxt_closed");
		sql.append(" LIMIT ?,?");
		// 通过工具包建立connection
		Connection con = JDBCUtil.getConnection();
		// 声明预编译器
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, (page - 1) * rows);
			pst.setInt(2, rows);
			ResultSet rs = pst.executeQuery();
			// 将查询出的每个User封装到一个User对象中并放入List
			while (rs.next()) {
				ClosedYR closedYR = new ClosedYR();
				closedYR.setCid(rs.getInt("cid"));
				closedYR.setMid(rs.getInt("mid"));
				closedYR.setUid(rs.getInt("uid"));
				closedYR.setUrole(rs.getInt("urole"));
				closedYR.setCdate(rs.getString("cdate"));
				closedYR.setCcontent(rs.getString("ccontent"));
				lc.add(closedYR);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lc;
	}

	@Override
	public int selallclouser() {
        int total=0;
         // SQL语句，查询被封记录总数
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT count(cid) FROM dcxt_closed");
		// 通过工具包建立connection
		Connection con = JDBCUtil.getConnection();
		// 声明预编译器
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				total = rs.getInt("count(cid)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public void clearrecordclo(int uid) {
		// sql语句,在colsed表中清除一条封号记录
		StringBuffer sql = new StringBuffer();
		sql.append("delete  FROM dcxt_closed");
		sql.append(" where uid=?");
		// 通过工具包建立connection
		Connection con = JDBCUtil.getConnection();
		// 声明预编译器
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, uid);
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				
		
	}

}
